package com.hxbd.test.handles;
/**
* <p>Title: DataRetrieveHandlers.java</p>
* <p>Description: </p>
* <p>Company: www.huoyunren.com</p>
* @author duanxiangyun
* @date 2016年6月24日
* @version 1.0
*/


import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.net.SocketException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

import javax.script.ScriptException;

import org.apache.commons.collections.ListUtils;
import org.apache.commons.collections4.Put;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.dom4j.Element;
import org.testng.Assert;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.duan.test.utils.Excel.DataHandlers;
import com.duan.test.utils.common.CommonUtils;
import com.duan.test.utils.common.JdbcUtils;
import com.hxbd.test.utils.InterfaceUtils;
import com.hxbd.test.utils.MsgSend;
import com.hxbd.test.utils.ReadConfigFromXml;
import com.hxbd.test.utils.RockectMqProduce;

public class DataRetrieveHandlers extends InterfaceUtils{
	/**
	 * 获得测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	static Object[] getTestDataMapFromExcel (String filename, String sheetname,Object totaldata[][],Object testdatas[][]) {
		if (!totaldata[6][1].toString().equalsIgnoreCase("run")) {
			Assert.fail("run运行标签必须在第二列");
		}
		if (!totaldata[6][2].toString().equalsIgnoreCase("level")) {
			Assert.fail("level必须在第三列");
		}
		String interfacedetail = totaldata[0][1].toString();
		String calltype = totaldata[1][1].toString();
		String url = totaldata[2][1].toString();
		String method = totaldata[3][1].toString();
		Map<String,String> returns = getReturns(filename, sheetname);
		List<Map<String,Object>> l = new ArrayList<>();
		for (int i = 0; i < testdatas.length; i++) {
			LinkedHashMap<String,Object> datamap = new LinkedHashMap<>();
			LinkedHashMap<String,String> testdata = new LinkedHashMap<>();
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {							
				for (int j = 0; j < 5+testdatas[0].length - 2; j++) {
					if (j == 0) {
						datamap.put("interfacedetail", interfacedetail);
					}
					else if (j == 1) {
						datamap.put("calltype", calltype);
					}
					else if (j == 2) {
						datamap.put("url", url);
					}
					else if (j == 3) {
						datamap.put("method", method);
					}
					else if (j==4){
						datamap.put("returns", returns);
					}
					else {
						Object o = testdatas[i][j-3];
						String s = totaldata[6][j-2].toString();
						if (!s.isEmpty()) {
							String value = o.toString();
//							if (value.contains("\\$\\{method\\}")) {
//								value = value.replaceAll("\\$\\{method\\}", method);
//							}
							testdata.put(s.toLowerCase(), value);
						}
					}
				}
				datamap.put("datamap", testdata);
				l.add(datamap);
			}
		}
		return l.toArray();
	}
	/**
	 * 获得接口测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	static List<Map<String, Object>> getInterTestDataMapFromExcel (String filename, String sheetname,Object totaldata[][],Object testdatas[][]) {
		if (!totaldata[5][1].toString().equalsIgnoreCase("run")) {
			Assert.fail("run运行标签必须在第二列");
		}
		if (!totaldata[5][2].toString().equalsIgnoreCase("level")) {
			Assert.fail("level必须在第三列");
		}
		String calltype = totaldata[0][1].toString();
		String url = totaldata[1][1].toString();
		String method = totaldata[2][1].toString();
		Map<String,String> returns = getReturns(totaldata);
		List<Map<String,Object>> l = new ArrayList<>();
		for (int i = 0; i < testdatas.length; i++) {
			LinkedHashMap<String,Object> datamap = new LinkedHashMap<>();
			LinkedHashMap<String,String> testdata = new LinkedHashMap<>();
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {							
				for (int j = 0; j < 4+testdatas[0].length - 2; j++) {
					if (j == 0) {
						datamap.put("calltype", calltype);
					}
					else if (j == 1) {
						datamap.put("url", url);
					}
					else if (j == 2) {
						datamap.put("method", method);
					}
					else if (j==3){
						datamap.put("returns", returns);
					}
					else {
						Object o = testdatas[i][j-2];
						String s = totaldata[5][j-1].toString();
						if (!s.isEmpty()) {
							String value = o.toString();
							testdata.put(s.toLowerCase(), value);
						}
					}
				}
				datamap.put("datamap", testdata);
				l.add(datamap);
			}
		}
		return l;
	}
	/**
	 * 获得接口测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	static Object[] getInterTestDataMapFromExcel (String filename, String sheetname,Object totaldata[][],Object testdatas[][],String testdesc, List<Map<String,Object>> ll) {
		if (!totaldata[5][1].toString().equalsIgnoreCase("run")) {
			Assert.fail("run运行标签必须在第二列");
		}
		if (!totaldata[5][2].toString().equalsIgnoreCase("level")) {
			Assert.fail("level必须在第三列");
		}
		String calltype = totaldata[0][1].toString();
		String url = totaldata[1][1].toString();
		String method = totaldata[2][1].toString();
		Map<String,String> returns = getReturns(filename, sheetname);
		List<Map<String,Object>> l = new ArrayList<>();
		for (int i = 0; i < testdatas.length; i++) {
			LinkedHashMap<String,Object> datamap = new LinkedHashMap<>();
			LinkedHashMap<String,String> testdata = new LinkedHashMap<>();
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {							
				for (int j = 0; j < 4+testdatas[0].length - 2; j++) {
					if (j == 0) {
						datamap.put("testdesc", testdesc);
					}
					if (j == 1) {
						datamap.put("calltype", calltype);
					}
					else if (j == 2) {
						datamap.put("url", url);
					}
					else if (j == 3) {
						datamap.put("method", method);
					}
					else if (j==4){
						datamap.put("returns", returns);
					}
					else {
						Object o = testdatas[i][j-3];
						String s = totaldata[5][j-2].toString();
						if (!s.isEmpty()) {
							String value = o.toString();
							testdata.put(s.toLowerCase(), value);
						}
					}
				}
				datamap.put("datamap", testdata);
				l.add(datamap);
			}
		}
		return l.toArray();
	}
	/**
	 * 获得测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	static Object[][] getTestDataFromExcelNewT (String filename, String sheetname,Object totaldata[][],Object testdatas[][]) {
		String interfacedetail = totaldata[0][1].toString();
		String calltype = totaldata[1][1].toString();
		String url = totaldata[2][1].toString();
		String method = totaldata[3][1].toString();
		Map<String,String> returns = getReturns(filename, sheetname);		
		List<Object[]> l = new ArrayList<Object[]>();
		for (int i = 0; i < testdatas.length; i++) {
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {		
				if (calltype.equalsIgnoreCase("rpc")) {
					
					Object [] tmpArr = new Object[5+testdatas[0].length - 2];
					for (int j = 0; j < tmpArr.length; j++) {
						if (j == 0) {
							tmpArr[j] = interfacedetail;
						}
						else if (j == 1) {
							tmpArr[j] = calltype;
						}
						else if (j == 2) {
							tmpArr[j] = url;
						}
						else if (j == 3) {
							tmpArr[j] = method;
						}
						else if (j==4){
							tmpArr[j] = returns;
						}
						else {
							Object o = testdatas[i][j-3];
							String s = totaldata[6][j-2].toString();
							if (s.equalsIgnoreCase("resultAlias") || s.equalsIgnoreCase("expectReturn")) {
								Map<String, String> mm = new HashMap<String, String>();
								mm.put(s, o.toString());
								tmpArr[j] = mm;
							}
							else if (!s.isEmpty()){
								tmpArr[j] = o;
							}
						}
					}
					l.add(tmpArr);
				}
				else if (calltype.contains("http")) {
					Object [] tmpArr = new Object[5+testdatas[0].length - 2];
					for (int j = 0; j < tmpArr.length; j++) {
						if (j == 0) {
							tmpArr[j] = interfacedetail;
						}
						else if (j == 1) {
							tmpArr[j] = calltype.replaceFirst("http\\.", "");
						}
						else if (j == 2) {
							tmpArr[j] = url;
						}
						else if (j==3){
							tmpArr[j] = method;
						}
						else if (j==4){
							tmpArr[j] = returns;
						}
						else {
							Object o = testdatas[i][j-3];
							String s = totaldata[6][j-2].toString();
							if (s.equalsIgnoreCase("resultAlias") || s.equalsIgnoreCase("expectReturn")) {
								Map<String, String> mm = new HashMap<String, String>();
								mm.put(s, o.toString());
								tmpArr[j] = mm;
							}
							else if (!s.isEmpty()){
								tmpArr[j] = o;
							}
						}
					}
					l.add(tmpArr);
				}
				
			}
		}
		Object [][] paramsArr = new Object [l.size()][];
		paramsArr = l.toArray(paramsArr);
		return paramsArr;
	}
	/**
	 * 获得测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	@SuppressWarnings("unchecked")
	static Object[][] getTestDataFromExcelNew (String filename, String sheetname,Object totaldata[][],Object testdatas[][]) {
		String interfacedetail = totaldata[0][1].toString();
		String proname = totaldata[1][1].toString();
		String url = totaldata[2][1].toString();
		String reqsttype = ReadConfigFromXml.getReqstType(proname);
		Map<String, Object> configmap = ReadConfigFromXml.readInterfaceConfig(proname);
		String method = totaldata[3][1].toString();
		Map<String,Integer> m = new HashMap<String,Integer>();
		if (reqsttype.equalsIgnoreCase("httpclient")) {
			Object patho = configmap.get("path");
			String path = null;
			if (patho != null) {
				path = patho.toString().replaceAll("\\$\\{method\\}", method);
			}
			if (path != null && !path.isEmpty()) {
				url += path;
			}
		}
		for (int i = 0; i < totaldata[4].length;i++) {
			Object param = totaldata[4][i];
			if (param != null && !param.toString().isEmpty()) {
				if (param.toString().equalsIgnoreCase("expectReturn") && i != 8) {
					Assert.fail("expectReturn列必须位于第9实列，位于用例描述之前");
				}
				if (param.toString().equalsIgnoreCase("resultAlias") && (totaldata[4].length == 10 ? i != 8 : i != 9)) {
					Assert.fail("resultAlias列必须位于第9或者第10实列，位于用例描述之前");
				}
				try{
					m.put(param.toString(),i);
				}
				catch(IllegalArgumentException e) {
					e.printStackTrace();
					Assert.fail("excel中作为数据源传入的参数不能有重复的");
				}	
			}
		}
		
		List<Object[]> l = new ArrayList<Object[]>();
		for (int i = 0; i < testdatas.length; i++) {
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {		
				if (reqsttype.equalsIgnoreCase("rpc")) {
					
					Object [] tmpArr = new Object[5+testdatas[0].length - 2];
					for (int j = 0; j < tmpArr.length; j++) {
						if (j == 0) {
							tmpArr[j] = interfacedetail;
						}
						else if (j == 1) {
							tmpArr[j] = reqsttype;
						}
						else if (j == 2) {
							tmpArr[j] = url;
						}
						else if (j == 3) {
							tmpArr[j] = method;
						}
						else if (j==4){
							tmpArr[j] = (Map<String, String>)configmap.get("return");
						}
						else {
							Object o = testdatas[i][j-3];
							String s = totaldata[4][j-2].toString();
							if (s.equalsIgnoreCase("resultAlias") || s.equalsIgnoreCase("expectReturn")) {
								Map<String, String> mm = new HashMap<String, String>();
								mm.put(s, o.toString());
								tmpArr[j] = mm;
							}
							else if (!s.isEmpty()){
								tmpArr[j] = o;
							}
						}
					}
					l.add(tmpArr);
				}
				else if (reqsttype.equalsIgnoreCase("httpclient")) {
					Object [] tmpArr = new Object[6+testdatas[0].length - 2];
					for (int j = 0; j < tmpArr.length; j++) {
						if (j == 0) {
							tmpArr[j] = interfacedetail;
						}
						else if (j == 1) {
							tmpArr[j] = reqsttype;
						}
						else if (j == 2) {
							tmpArr[j] = url;
						}
						else if (j==3){
							tmpArr[j] = configmap.get("sendtype").toString();
						}
						else if (j==4){
							tmpArr[j] = (Map<String, String>)configmap.get("return");
						}
						else if (j==5) {
							tmpArr[j] = (Map<String, String>) configmap.get("header");
						}
						else if (j==6) {
							Map<String,String> params = (Map<String, String>) configmap.get("body");
							Map<String,String> params_copy = new HashMap<String,String>();
							params_copy.putAll(params);
							Map<String,String> tmpmap = new HashMap<String,String>();
							loop:
							for (String key : params_copy.keySet()) {
								String value = params_copy.get(key);
								if (value.contains("(") && value.endsWith(")")) {
									String tmp[] = value.substring(value.indexOf("(")+1, value.indexOf(")")).split(",");
									for (int k = 0; k < tmp.length; k++) {
										while(params_copy.containsKey(tmp[k])) {
											tmp[k] = params_copy.get(tmp[k]);
										}
									}
									value = value.substring(0, value.indexOf("(")+1) + StringUtils.join(tmp, ",") + ")";
								}
								else {
									while(params_copy.containsKey(value)) {
										value = params_copy.get(value);
									}
								}
								value = value.replaceAll("\\$\\{method\\}", method).replaceAll("\\$\\{jsondata\\}", testdatas[i][2].toString());
								params_copy.put(key, value);
							}
							JSONObject jo = new JSONObject();
							try{
								jo = JSONObject.parseObject(testdatas[i][2].toString());
							}
							catch(JSONException e) {
								e.printStackTrace();
								Assert.fail("testdata格式有误，请传入正确的JSON格式");
							}
							for (Object jo_key : jo.keySet()) {
								tmpmap.put(jo_key.toString(), jo.get(jo_key).toString());
							}
							if (Boolean.valueOf(((Element)ReadConfigFromXml.getSingleElementFromXpath("//project[@name='DSP']/body")).attributeValue("datatobody"))) {
								params_copy.putAll(tmpmap);
							}
							tmpArr[j] = params_copy;
						}
						else {
							Object o = testdatas[i][j-4];
							String s = totaldata[4][j-3].toString();
							if (s.equalsIgnoreCase("resultAlias") || s.equalsIgnoreCase("expectReturn")) {
								Map<String, String> mm = new HashMap<String, String>();
								mm.put(s, o.toString());
								tmpArr[j] = mm;
							}
							else if (!s.isEmpty()){
								tmpArr[j] = o;
							}
						}
					}
					l.add(tmpArr);
				}
				
			}
		}
		Object [][] paramsArr = new Object [l.size()][];
		paramsArr = l.toArray(paramsArr);
		return paramsArr;
	}
	/**
	 * 获得测试数据的公共方法
	 * @param filename
	 * @param sheetname
	 * @param totaldata 该case所有URL、参数及数据
	 * @param testdatas 该case所有数据
	 * @return Object[][]
	 */
	static Object[][] getTestDataFromExcel (String filename, String sheetname,Object totaldata[][],Object testdatas[][]) {
		String interfacedetail = totaldata[0][1].toString();
		String url = totaldata[1][1].toString();
		String sendMethod = totaldata[2][1].toString();
		Boolean dataToJson = Boolean.parseBoolean(totaldata[3][1].toString());
		Map<String,Integer> m = new HashMap<String,Integer>();
		Map<String,String> params = new HashMap<String,String>();
		for (int i = 0; i < totaldata[4].length;i++) {
			Object param = totaldata[4][i];

			if (param != null && !param.toString().isEmpty()) {
				if (param.toString().equalsIgnoreCase("expectResult") && i != 8) {
					Assert.fail("expectResult列必须位于第9实列，位于用例描述之前");
				}
				if (param.toString().equalsIgnoreCase("resultAlias") && (totaldata[4].length == 10 ? i != 8 : i != 9)) {
					Assert.fail("resultAlias列必须位于第9或者第10实列，位于用例描述之前");
				}
				try {
					params.put(param.toString(), totaldata[5][i].toString());
				}
				catch(IllegalArgumentException e) {
					e.printStackTrace();
					Assert.fail("excel中传入接口的参数不能有重复的");
				}	
			}
		}
		for (int i = 0; i < totaldata[6].length;i++) {
			Object param = totaldata[6][i];
			if (param != null && !param.toString().isEmpty()) {
				try{
					m.put(param.toString(),i);
				}
				catch(IllegalArgumentException e) {
					e.printStackTrace();
					Assert.fail("excel中作为数据源传入的参数不能有重复的");
				}	
			}
		}
		
		List<Object[]> l = new ArrayList<Object[]>();
		for (int i = 0; i < testdatas.length; i++) {
			if (testdatas[i][0].toString().equalsIgnoreCase("N")) {
				continue;
			}
			else if (InterfaceUtils.getTlevel().equals("1") && !testdatas[i][1].equals("1")) {
				continue;
			}
			else {				
				Object [] tmpArr = new Object[4+testdatas[0].length - 2];
				for (int j = 0; j < tmpArr.length; j++) {
					if (j == 0) {
						tmpArr[j] = interfacedetail;
					}
					else if (j == 1) {
						tmpArr[j] = url;
					}
					else if (j==2){
						tmpArr[j] = sendMethod;
					}
					else if (j==3) {
						Map<String,String> params_copy = new HashMap<String,String>();
						params_copy.putAll(params);
						Map<String,String> tmpmap = new HashMap<String,String>();
						String removekey = null;
						loop:
						for (String key : params_copy.keySet()) {
							String value = params_copy.get(key);
							if (value.contains("(") && value.endsWith(")")) {
								String tmp[] = value.substring(value.indexOf("(")+1, value.indexOf(")")).split(",");
								for (int k = 0; k < tmp.length; k++) {
									while(params_copy.containsKey(tmp[k])) {
										tmp[k] = params_copy.get(tmp[k]);
									}
									while(m.containsKey(tmp[k])) {
										if (m.get(tmp[k]) == 3) {
											tmp[k] = testdatas[i][2].toString();
										}	
									}
								}
								value = value.substring(0, value.indexOf("(")+1) + StringUtils.join(tmp, ",") + ")";
								params_copy.put(key, value);
							}
							else {
								while(params_copy.containsKey(value)) {
									value = params_copy.get(value);
								}
								while(m.containsKey(value)) {
									if (m.get(value) == 3) {									
										if (dataToJson) {
											value = testdatas[i][2].toString();
										}
										else {
											removekey = key;																						
											continue loop;
										}
									}
								}
								params_copy.put(key, value);
							}
						}
						JSONObject jo = new JSONObject();
						try{
							jo = JSONObject.parseObject(testdatas[i][2].toString());
						}
						catch(JSONException e) {
							e.printStackTrace();
							Assert.fail("testdata格式有误，请传入正确的JSON格式");
						}
						for (Object jo_key : jo.keySet()) {
							tmpmap.put(jo_key.toString(), jo.get(jo_key).toString());
						}
						if (removekey != null) {
							params_copy.remove(removekey);
						}
						if (!dataToJson) {
							params_copy.putAll(tmpmap);
						}
						tmpArr[j] = params_copy;
					}
					else {
						Object o = testdatas[i][j-2];
						String s = totaldata[6][j-1].toString();
						if (s.equalsIgnoreCase("resultAlias") || s.equalsIgnoreCase("expectReturn")) {
							Map<String, String> mm = new HashMap<String, String>();
							mm.put(s, o.toString());
							tmpArr[j] = mm;
						}
						else if (!s.isEmpty()){
							tmpArr[j] = o;
						}
					}
				}
				l.add(tmpArr);
			}
		}
		Object [][] paramsArr = new Object [l.size()][];
		paramsArr = l.toArray(paramsArr);
		return paramsArr;
	}
	/**
	 * 获得excel表单个接口各个场景的测试数据
	 * @param filename 文件名
	 * @param sheetname 表名
	 * @return Object[][]
	 * @throws Exception 
	 */
	public static Object[] getTestData (String filename, String sheetname){
		Object totaldata[][] = null;
//		Object [][] testdatas = DataHandlers.getInstance(filename).getData(sheetname, 7,-1, 1,-1,true);
		Object[][] testdatas = null;
		try {
			totaldata = DataHandlers.getInstance(filename).getData(sheetname,0,-1,0,-1,true);
			testdatas = DataHandlers.getInstance(filename).getData(sheetname, 7,-1, 1,-1,true);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			Assert.fail(e.toString());
		}
		return getTestDataMapFromExcel(filename, sheetname, totaldata, testdatas);
	}
	public static Map<String,String> getReturns (String filename, String sheetname) {
		Map<String,String> returnsmap = new HashMap<>();
		try {
			Object[][] paramsarr = DataHandlers.getInstance(filename).getData(sheetname, 3,1, 1,-1,true);
			int colnum = delArrayEptVal(paramsarr[0]).length;
			Object returns[][] = DataHandlers.getInstance(filename).getData(sheetname, 3,2, 1,colnum,true);
			for (int j = 0; j < returns[0].length; j++) {
				returnsmap.put(returns[0][j].toString(), returns[1][j].toString());
			}			
		}
		catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			Assert.fail(e.toString());
		}
		return  returnsmap;
	}
	public static Map<String,String> getReturns (Object[][] totaldata) {
		Map<String,String> returnsmap = new HashMap<>();
		int colnum = delArrayEptVal(totaldata[3]).length;
		for (int j = 1; j < colnum; j++) {
			returnsmap.put(totaldata[3][j].toString(), totaldata[4][j].toString());
		}			
		return  returnsmap;
	}
	@SuppressWarnings("unchecked")
	public static <T> T[] delArrayEptVal(T[] arr) {
		List<T> l = new ArrayList<T>(Arrays.asList(arr));
		for (Iterator<T> i = l.iterator(); i.hasNext();) {
			T o = i.next();
			if (o == null || (o instanceof String && o.toString().isEmpty())) {
				i.remove();
			}
		}
		return (T[])(l.toArray());
	}
	
	/**
	 * 场景组合测试
	 * @param filename
	 * @param sheetname
	 * @return 
	 * @throws Exception
	 */
	public static Object[] getDuplicateSceneTestData (String filename, String sheetname){
		List<List<Map<String,Object>>> returnlist = new ArrayList<>();
		List<Map<String,Object>> ll = new ArrayList<>();
		List<Map<String,Object>> intertestlist = new ArrayList<>();
		Object[][] indexs = null;
		try {
			indexs = DataHandlers.getInstance(filename).getData(sheetname, 0,-1, 0,1,true);
			List<Integer> l = new ArrayList<Integer>();
			for (int i = 0; i < indexs.length; i++) {
				if (!indexs[i][0].toString().isEmpty())
				{
					l.add(i);
				}
					
			}
			Object [] arr = l.toArray();
			Arrays.sort(arr);
			Map<Integer,Integer> nums = new LinkedHashMap<Integer, Integer>();
			for (int i = 0; i < arr.length; i++) {
				int int1 = Integer.parseInt(arr[i].toString());
				int int2 = -1;
				if (i == arr.length - 1) {
				}
				else {
					int2 = Integer.parseInt(arr[i+1].toString()) - int1;
				}
				nums.put(int1, int2);
			}
			int countintertest = 0;
			int interfacetestindex = 0;
			Boolean isInterfaceTest = false;
			Boolean tmp = true;
			for (Integer i : nums.keySet()) {
				String indexname = indexs[i][0].toString();
				if (indexname.equalsIgnoreCase("interfaceTest")) {
					isInterfaceTest = true;
					tmp = false;
					countintertest++;
				}
				else if(tmp){
					interfacetestindex++;
				}
			}
			if (countintertest > 1) {
				Assert.fail("interfacetest项只能有一个");
			}
//			if (isInterfaceTest) {
				int index = 0;
				for (Integer i : nums.keySet()) {
					Integer value = nums.get(i);
					String indexname = indexs[i][0].toString();
					if (indexname.equalsIgnoreCase("testdesc")) {
						String interdesc = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,1,true)[0][0].toString();
						Map<String,Object> tmpm = new HashMap<>();
						tmpm.put(indexname.toLowerCase(), interdesc);
						ll.add(index,tmpm);
						index++;
					}
					else if (indexname.equalsIgnoreCase("interfacetest")) {
						Object[][] paramsarr = DataHandlers.getInstance(filename).getData(sheetname, i+5,1, 1,-1,true);
						int colnum = DataRetrieveHandlers.delArrayEptVal(paramsarr[0]).length;
						Object interfaceTotalData[][] = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,colnum,true);
						Object[][] testdata = DataHandlers.getInstance(filename).getData(sheetname, i+6,value-6, 2,colnum-2,true);
						intertestlist = DataRetrieveHandlers.getInterTestDataMapFromExcel(filename, sheetname, interfaceTotalData, testdata);
						ll.add(index,null);
						index++;
					}
					else if (indexname.startsWith("sleep")) {
						long sleeptime;
						sleeptime = Long.parseLong(DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,1,true)[0][0].toString());
						Map<String,Object> tmpm = new HashMap<>();
						tmpm.put(indexname.toLowerCase(), sleeptime);
						ll.add(index,tmpm);
						index++;
					}
					else if (indexname.startsWith("Assert")) {
						String assertstr = null;					
						assertstr = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,1,true)[0][0].toString();
						Map<String,Object> tmpm = new HashMap<>();
						tmpm.put(indexname.toLowerCase(), assertstr);
						ll.add(index,tmpm);
						index++;
					}
					else if (indexname.startsWith("interface") && !indexname.equalsIgnoreCase("interfacetest")) {
						Object[][] paramsarr = DataHandlers.getInstance(filename).getData(sheetname, i+5,1, 1,-1,true);
						int colnum = DataRetrieveHandlers.delArrayEptVal(paramsarr[0]).length;
						Object interfaceTotalData[][] = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,colnum,true);
						Object[][] testdata = DataHandlers.getInstance(filename).getData(sheetname, i+6,value-6, 2,colnum-2,true);
						Map<String,Object> tmpm = new HashMap<>();
						tmpm.put(indexname.toLowerCase(), DataRetrieveHandlers.getInterTestDataMapFromExcel(filename, sheetname, interfaceTotalData, testdata));
						ll.add(index,tmpm);
						index++;
					}
					else {			
						Map<String,Object> tmpm = new HashMap<>();
						tmpm.put(indexname.toLowerCase(), DataHandlers.getInstance(filename).getData(sheetname, i+1,value-1, 1,-1,true));
						ll.add(index,tmpm);
						index++;
					}				
				}	
				if (isInterfaceTest) {
					for (Map<String,Object> m : intertestlist) {
						List<Map<String, Object>> ll_copy = new ArrayList<>();
						ll_copy.addAll(ll);
						Map<String,Object> tmpm = new HashMap<>();
						List<Map<String,Object>> tmpl = new ArrayList<>();
						tmpl.add(m);
						tmpm.put("interfacetest",tmpl);
						ll_copy.set(interfacetestindex,tmpm);
						returnlist.add(ll_copy);
					}
				}
				else {
					returnlist.add(ll);
				}
				return returnlist.toArray();
//			}
		}catch (Exception e) {
			// TODO: handle exception
			Assert.fail(e.toString());
			return null;
		}
		
	}
	
	public static File file = null;
	static {
		file = new File(CommonUtils.getFilePath("/sql.log"));
	    if (file.exists()) {
	    	file.delete(); 
	    }
	    try {
			file.createNewFile();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			Assert.fail("创建sql日志文件失败");
		}
	}	
	/**
	 * 场景组合测试
	 * @param filename
	 * @param sheetname
	 * @throws Exception
	 */
	private void duplicateSceneTest (final String filename, String sheetname) throws Exception{
//		Object totaldata[][] = DataHandlers.getInstance(filename).getData(sheetname);
		Object[][] indexs = null;
		try {
			indexs = DataHandlers.getInstance(filename).getData(sheetname, 0,-1, 0,1,true);
		} catch (Exception e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		List<Integer> l = new ArrayList<Integer>();
		for (int i = 0; i < indexs.length; i++) {
			if (!indexs[i][0].toString().isEmpty())
			{
				l.add(i);
			}
				
		}
		Object [] arr = l.toArray();
		Arrays.sort(arr);
		Map<Integer,Integer> nums = new LinkedHashMap<Integer, Integer>();
		for (int i = 0; i < arr.length; i++) {
			int int1 = Integer.parseInt(arr[i].toString());
			int int2 = -1;
			if (i == arr.length - 1) {
			}
			else {
				int2 = Integer.parseInt(arr[i+1].toString()) - int1;
			}
			nums.put(int1, int2);
		}
		for (Integer i : nums.keySet()) {
			Integer value = nums.get(i);
			Object currentCaseTotaldata[][] = null;
			String indexname = indexs[i][0].toString();
			if (indexname.equalsIgnoreCase("testdesc")) {
				
			}
			if (indexname.equalsIgnoreCase("sleep")) {
				long sleeptime;
				try {
					sleeptime = Long.parseLong(DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,1,true)[0][0].toString());
					Thread.sleep(sleeptime);
				} catch (NumberFormatException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}catch (Exception e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			else if (indexname.equalsIgnoreCase("Assert")) {
				String assertstr = null;
				try {
					assertstr = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,1,true)[0][0].toString();
				} catch (Exception e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				Map<String, Object> m = getActualrm();
				for (String key : m.keySet()) {
					if (assertstr.contains(key)) {
						Object mapvalue = m.get(key);
						if (mapvalue == null) {
							mapvalue = "";
						}
						assertstr = assertstr.replaceAll(key, mapvalue.toString());
					}
				}
				try {
					assertstr = invokeAllMethod(assertstr);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					Assert.fail("反射执行方法失败", e.fillInStackTrace());
				}
				try {
					checkAssert(assertstr);
				} catch (ScriptException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					Assert.fail("断言方法执行失败",e.fillInStackTrace());
				}
			}
			else if (indexname.equalsIgnoreCase("interface")) {
				Object[][] paramsarr = DataHandlers.getInstance(filename).getData(sheetname, i+4,1, 1,-1,true);
				int colnum = DataRetrieveHandlers.delArrayEptVal(paramsarr[0]).length;
				Object interfaceTotalData[][] = DataHandlers.getInstance(filename).getData(sheetname, i,value, 1,colnum,true);
				Object[][] testdata = DataHandlers.getInstance(filename).getData(sheetname, i+5,value-5, 2,colnum-2,true);
				currentCaseTotaldata = DataRetrieveHandlers.getTestDataFromExcelNew(filename, sheetname, interfaceTotalData, testdata);
				for (int j = 0; j < currentCaseTotaldata.length; j++) {
					interfaceTestOld(filename, currentCaseTotaldata[j]);
				}
			}
			else {			
				try {
					currentCaseTotaldata = DataHandlers.getInstance(filename).getData(sheetname, i+1,value-1, 1,-1,true);
				} catch (Exception e2) {
					// TODO Auto-generated catch block
					e2.printStackTrace();
				}
				if (indexname.equalsIgnoreCase("mq")) {
					final AtomicInteger count = new AtomicInteger();
					final Object[][] currentCaseTotaldata_mq = currentCaseTotaldata;
					 Vector<Thread> threads = new Vector<Thread>();  
					for (int j = 0; j < currentCaseTotaldata_mq.length; j++) {
						Thread t = new Thread(new Runnable() {
							@Override
							public void run() {
								count.incrementAndGet();
								int index = count.get() - 1;
								Object []mqarr = (Object[])currentCaseTotaldata_mq[index];
								String key = currentCaseTotaldata_mq[index][10].toString();								
								Map<Integer, Date> mq_timemap = new HashMap<Integer, Date>();	
								File filepath = getRelativePathFile(filename, mqarr[7].toString());	
								// rocketmq中发送消息
								try {
									Thread.sleep(3000);
								} catch (InterruptedException e1) {
									// TODO Auto-generated catch block
									e1.printStackTrace();
								}
								RockectMqProduce rmq = new RockectMqProduce(
										"autotest"+index+"_"+now("yyyyMMddHHmmss"), mqarr[0].toString(),
										"autotest_"+now());
								try {
									rmq.start();
									getRmqmap().add(rmq);
									//根据文件内容发送
									mq_timemap = rmq.sendMsgFromFile(filepath.toString(),mqarr[1].toString(), mqarr[2].toString(), mqarr[3].toString(),invokeAllMethod(mqarr[4].toString()),mqarr[5].toString(),Integer.parseInt(mqarr[6].toString()),Integer.parseInt(mqarr[8].toString()),Integer.parseInt(mqarr[9].toString()),0);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
									Assert.fail("插入MQ异常", e.fillInStackTrace());
								}
								InterfaceUtils.getMsg_map().put(key, mq_timemap);
							}
						});
						threads.add(t);
						t.start();
					}
					for (Thread iThread : threads) {  
					      try {  
					        // 等待所有线程执行完毕  
					        iThread.join();  
					      } catch (InterruptedException e) {  
					        e.printStackTrace();  
					      }  
					}  
				}
				else if (indexname.equalsIgnoreCase("sendmsg")) {
					final AtomicInteger count = new AtomicInteger();
					ThreadPoolExecutor pool = new ThreadPoolExecutor(

						    20,100,600,

						    TimeUnit.SECONDS, 

						    new ArrayBlockingQueue<Runnable>(5),

						    new ThreadPoolExecutor.CallerRunsPolicy());
					final Object[][] currentCaseTotaldata1 = currentCaseTotaldata;				
					for (int j = 0; j < currentCaseTotaldata1.length; j++) {
						Runnable r = new Runnable() {
							@Override
							public void run() {
								count.incrementAndGet();
								int index = count.get() - 1;
								String sendMethod = currentCaseTotaldata1[index][0].toString().trim();
								String msgadd = currentCaseTotaldata1[index][1].toString().trim();
								String portstr = currentCaseTotaldata1[index][2].toString().trim();
								String sendtime = currentCaseTotaldata1[index][3].toString().trim();
								if (sendtime.contains("(") && sendtime.contains(")")) {
									try {
										sendtime = InterfaceUtils.invokeAllMethod(sendtime);
									} catch (IllegalArgumentException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
										Assert.fail("反射执行方法失败",e.fillInStackTrace());
									} catch (IllegalAccessException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
										Assert.fail("反射执行方法失败",e.fillInStackTrace());
									} catch (InvocationTargetException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
										Assert.fail("反射执行方法失败",e.fillInStackTrace());
									} catch (InstantiationException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
										Assert.fail("反射执行方法失败",e.fillInStackTrace());
									}

								}
								String timestamp = currentCaseTotaldata1[index][4].toString().trim();
								int internal = Integer.parseInt(currentCaseTotaldata1[index][5].toString().trim());
								String msg = currentCaseTotaldata1[index][6].toString().trim();
								String startnum = currentCaseTotaldata1[index][7].toString().trim();
								String sendcount = currentCaseTotaldata1[index][8].toString().trim();
								String key = currentCaseTotaldata1[index][9].toString().trim();
								List<String> msglist = new ArrayList<String>();
								if (msg.endsWith(".txt")) {
									try {
										List<String> list = FileUtils.readLines(getRelativePathFile(filename, msg), "UTF-8");
										if (startnum.isEmpty()) {
											startnum = "1";
										}
										int tmpint = Integer.parseInt(startnum)-1;
										int count = 0;
										if (sendcount.isEmpty()) {
											count = list.size() - tmpint;
										}
										else {
											count = Integer.parseInt(sendcount);
										}
										for (int k = tmpint; k < tmpint+count; k++) {
											msglist.add(list.get(k));
										}
									} catch (IOException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
										Assert.fail("读取报文文件失败");
									}
								}
								else {
									msglist = Arrays.asList(msg.split("\n"));
								}
								MsgSend ms = new MsgSend();
								Map<Integer, Date> m_samrt = new HashMap<Integer, Date>();
								//发送mqtt报文时，地址填url;模式(例如http:tx.huoyunren.com;1。模式：1为固定式，0为便携式)，端口填topic;imei(例如topic_gps_location;107015090000007)。
								if (sendMethod.equals("sendMqttMsg")) {
//									try {
//										m_samrt = ms.sendMqttMsg(msgadd.split(";")[0], portstr.split(";")[0], msglist, portstr.split(";")[1], msgadd.split(";")[1],sendtime, timestamp, internal);
//									} catch (IOException e) {
//										// TODO Auto-generated catch block
//										e.printStackTrace();
//									}
								}
								else {
									int port = Integer.parseInt(portstr);
									if (sendMethod.equals("sendMsg")) {
										m_samrt = ms.sendMsg(msgadd, port, msglist, sendtime, timestamp, internal);
									}
									else if (sendMethod.equals("sendString")) {
										m_samrt = ms.sendString(msgadd, port, msglist, sendtime, timestamp, internal);
									}
									else if (sendMethod.equals("sendStringOnAndOff")) {
										m_samrt = ms.sendStringOnAndOff(msgadd, port, msglist, sendtime,timestamp, internal);
									}
									else if (sendMethod.equals("sendUDP")) {
										try {
											m_samrt = ms.sendUDP(msgadd, port, msglist, sendtime, timestamp, internal);
										} catch (SocketException e) {
											// TODO Auto-generated catch block
											e.printStackTrace();
										}
									}
								}
								InterfaceUtils.getMsg_map().put(key, m_samrt);
							}
						};
						pool.execute(r);
					}
					pool.shutdown();// 只是不能再提交新任务，等待执行的任务不受影响
					try {
						boolean loop = true;
						do { // 等待所有任务完成
							loop = !pool.awaitTermination(10, TimeUnit.MINUTES); // 阻塞，直到线程池里所有任务结束
						} while (loop);
					} catch (InterruptedException e) {
						e.printStackTrace();
					}
				}
				else if (indexname.equalsIgnoreCase("mysql")) {
					for (int j = 0; j < currentCaseTotaldata.length; j++) {
						String resultnum = currentCaseTotaldata[j][4].toString().trim();
						String sqlsheet = currentCaseTotaldata[j][5].toString().trim();
						String checkkey = currentCaseTotaldata[j][6].toString().trim();
						String notcheckkey = currentCaseTotaldata[j][7].toString().trim();
						List<Map<String, String>> sqllist;						
						try {
							sqllist = getSqlResult(filename, sqlsheet);
							sqlExcute(j+1,currentCaseTotaldata[j][0].toString().trim(), currentCaseTotaldata[j][1].toString().trim(), currentCaseTotaldata[j][2].toString().trim(), invokeAllMethod(currentCaseTotaldata[j][3].toString().trim()),resultnum,sqllist,checkkey,notcheckkey,file);
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							Assert.fail("sql日志文件读写失败");
						} catch (IllegalArgumentException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							Assert.fail("反射执行方法失败");
						} catch (IllegalAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							Assert.fail("反射执行方法失败");
						} catch (InvocationTargetException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							Assert.fail("反射执行方法失败");
						} catch (InstantiationException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							Assert.fail("反射执行方法失败");
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				}
				else if (indexname.equalsIgnoreCase("mongo")) {
					
				}
				else if (indexname.equalsIgnoreCase("kafka")) {
					
				}
			}
			
		}	
	}
	
	//数据库查询
	public static void sqlExcute(int index, String url,String usr,String pwd,String sql,String resultnum, List<Map<String,String>> expectresult, String checkkey, String notcheckkey, File outfile) throws IOException{
		
		JdbcUtils jdbc = new JdbcUtils("com.mysql.jdbc.Driver","jdbc:mysql://"+url+":3306?zeroDateTimeBehavior=convertToNull&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&useUnicode=true&characterEncoding=UTF-8", usr, pwd);
		try {
			List<Map<String, String>> result = jdbc.findModeStringResult(sql);
			FileUtils.writeStringToFile(outfile, "数据库测试场景"+index+":"+now()+"<===================数据库："+url+",查询语句:"+sql+",返回数据如下:====================>\r\n","UTF-8", true);
			FileUtils.writeLines(outfile, "UTF-8", result, true);
			if (!resultnum.isEmpty()) {
				Assert.assertEquals(result.size(), Integer.parseInt(resultnum));
			}
			if (!checkkey.isEmpty()) {
				List<Map<String, String>> l1 = new ArrayList<Map<String,String>>();
				List<Map<String, String>> l2 = new ArrayList<Map<String,String>>();
				for (int i = 0; i < result.size(); i++) {
					Map<String, String> m = result.get(i);
					Map<String, String> m1 = new HashMap<String, String>();
					if (m.containsKey(checkkey)) {
						m1.put(checkkey, m.get(checkkey));
					}
					l1.add(m1);
				}
				for (int i = 0; i < expectresult.size(); i++) {
					Map<String, String> m = expectresult.get(i);
					Map<String, String> m2 = new HashMap<String, String>();
					if (m.containsKey(checkkey)) {
						m2.put(checkkey, m.get(checkkey));
					}
					l2.add(m2);
				}
				result = l1;
				expectresult = l2;
			}
			else if (!notcheckkey.isEmpty()) {
				for (int i = 0; i < result.size(); i++) {
					Map<String, String> m = result.get(i);
					if (m.containsKey(notcheckkey)) {
						m.remove(notcheckkey);
					}
				}
				for (int i = 0; i < expectresult.size(); i++) {
					Map<String, String> m = expectresult.get(i);
					if (m.containsKey(notcheckkey)) {
						m.remove(notcheckkey);
					}
				}
			}	
			if (null != expectresult && !expectresult.isEmpty()){
				List<Map<String,Object>> ll= ListUtils.sum(result, expectresult);
				Assert.assertTrue(ListUtils.isEqualList(result, ll),"数据库测试场景"+index+":数据库实际返回结果不完全包含期望结果，测试失败");
			}
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			Assert.fail("数据库执行失败");
		} 
		finally{
			jdbc.closeDBResource();
		}
	}
	public static List<Map<String,String>> getSqlResult(String excelname, String sheetname) throws Exception {
		Object [][]keys = DataHandlers.getInstance(excelname).getData(sheetname, 0, 1, 0, -1, true);
		Object [][]data = DataHandlers.getInstance(excelname).getData(sheetname, 1, -1, 0, -1, true);
		List<Map<String, String>> l = new ArrayList<Map<String, String>>();
		for (int i = 0; i < data.length; i++) {
			Map<String,String> m = new HashMap<String,String>();
			for (int j = 0; j < keys[0].length; j++) {
				Object value = data[i][j];
				m.put(keys[0][j].toString(), value.toString());
			}
			l.add(m);
		}
		return l;
	}
}
